package org.dusin.util;

import java.util.List;

/**
 * @author XiXiu
 */
public class SqlUtil {
    //根据多种条件拼凑where从句，并填写参数（？替换的值）
    public static String buildWhere(Integer ID, String companyCode, String factoryCode, String dateFrom, String dateTo, String key, List<Object> argList) {
        StringBuilder sb = new StringBuilder();
        //List<Object> argList = new ArrayList<Object>(); //组织参数值
        if (ID != null) {
            sb.append(" and ID>?");
            argList.add(ID);
        }
        if (companyCode != null && !"".equals(companyCode) && !"请选择供应商".equals(companyCode)) {
            sb.append(" and companyCode=?");
            argList.add(companyCode);
        }
        if (factoryCode != null && !"".equals(factoryCode) && !"请选择客户".equals(factoryCode)) {
            sb.append(" and factoryCode=?");
            argList.add(factoryCode);
        }
        //有开始日期，没有结束日期，则查询当前开始日期的记录
        /*if((dateFrom!=null&&!dateFrom.trim().equals(""))&&(dateTo==null||dateTo.trim().equals(""))){
         if(dateFrom.length()<12){
         //dateFrom = dateFrom.replaceAll("-", "");
         sb.append(" and ");
         sb.append(" substr(arrivalTime,1,10)=?");
         argList.add(dateFrom);
         }else{  //目前这里给大屏查询用
         //dateFrom = dateFrom.replaceAll("[-: ]", "");
         sb.append(" and ");
         sb.append(" arrivalTime>?");
         argList.add(dateFrom);
         }
         }else if((dateFrom!=null&&!dateFrom.trim().equals(""))&&(dateTo!=null&&!dateTo.trim().equals(""))){
         //开始日期和结束日期都有
         //dateFrom = dateFrom.replaceAll("-", "");
         dateTo = dateTo+" 24:00:00";
         sb.append(" and ");
         sb.append(" arrivalTime>? and arrivalTime<?");
         argList.add(dateFrom);
         argList.add(dateTo);
         }else if((dateTo!=null&&!dateTo.trim().equals(""))&&(dateFrom==null||dateFrom.trim().equals(""))){
         //只有结束日期，则查询结束日期当天的数据
         //dateTo = dateTo.replaceAll("-", "");
         sb.append(" and ");
         sb.append(" substr(arrivalTime,1,10)=?");
         argList.add(dateTo);
         }*/
        //时间查询以磅单时间为依据
        if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo == null || dateTo.trim().equals(""))) {

            //dateFrom = dateFrom.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateFrom);

        } else if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo != null && !dateTo.trim().equals(""))) {
            //开始日期和结束日期都有
            // dateFrom = dateFrom.replaceAll("-", "");
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime>=? and paperTime<=?");
            argList.add(dateFrom);
            argList.add(dateTo);
        } else if ((dateTo != null && !dateTo.trim().equals("")) && (dateFrom == null || dateFrom.trim().equals(""))) {
            //只有结束日期，则查询结束日期当天的数据
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateTo);
        }
        //模糊查询
        if (key != null && !key.equals("")) {
            sb.append(" and ");
            sb.append(" (plateNo like ? or siterName like ? or factoryName like ? or productName like ? or companyName like ?) ");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
        }
        return sb.toString();
    }

    public static String buildWhere4BusinessPaper(Integer ID, String companyCode, String factoryCode, String dateFrom, String dateTo, String key, List<Object> argList) {
        StringBuilder sb = new StringBuilder();
        //List<Object> argList = new ArrayList<Object>(); //组织参数值
        if (ID != null) {
            sb.append(" and ID>?");
            argList.add(ID);
        }
        if (companyCode != null && !"".equals(companyCode) && !"请选择供应商".equals(companyCode)) {
            sb.append(" and companyCode=?");
            argList.add(companyCode);
        }
        if (factoryCode != null && !"".equals(factoryCode) && !"请选择客户".equals(factoryCode)) {
            sb.append(" and factoryCode=?");
            argList.add(factoryCode);
        }
        //时间查询以磅单时间为依据
        if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo == null || dateTo.trim().equals(""))) {

            //dateFrom = dateFrom.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateFrom);

        } else if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo != null && !dateTo.trim().equals(""))) {
            //开始日期和结束日期都有
            // dateFrom = dateFrom.replaceAll("-", "");
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime>=? and paperTime<=?");
            argList.add(dateFrom);
            argList.add(dateTo);
        } else if ((dateTo != null && !dateTo.trim().equals("")) && (dateFrom == null || dateFrom.trim().equals(""))) {
            //只有结束日期，则查询结束日期当天的数据
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateTo);
        }
        //模糊查询
        if (key != null && !key.equals("")) {
            sb.append(" and ");
            sb.append(" (plateNo like ? or siterName like ? or factoryName like ? or productName like ? or companyName like ? or supplierName like ?) ");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
        }
        return sb.toString();
    }

    public static String buildWhere4Acargo(Integer ID, String companyCode, String factoryCode, String dateFrom, String dateTo, String key, List<Object> argList) {
        StringBuilder sb = new StringBuilder();
        //List<Object> argList = new ArrayList<Object>(); //组织参数值
        if (ID != null) {
            sb.append(" and ID>?");
            argList.add(ID);
        }
        if (companyCode != null && !"".equals(companyCode) && !"请选择供应商".equals(companyCode)) {
            sb.append(" and companyCode=?");
            argList.add(companyCode);
        }
        if (factoryCode != null && !"".equals(factoryCode) && !"请选择客户".equals(factoryCode)) {
            sb.append(" and factoryCode=?");
            argList.add(factoryCode);
        }
        //时间查询以磅单时间为依据
        if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo == null || dateTo.trim().equals(""))) {

            //dateFrom = dateFrom.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateFrom);

        } else if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo != null && !dateTo.trim().equals(""))) {
            //开始日期和结束日期都有
            // dateFrom = dateFrom.replaceAll("-", "");
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime>=? and paperTime<=?");
            argList.add(dateFrom);
            argList.add(dateTo);
        } else if ((dateTo != null && !dateTo.trim().equals("")) && (dateFrom == null || dateFrom.trim().equals(""))) {
            //只有结束日期，则查询结束日期当天的数据
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateTo);
        }
        //模糊查询
        if (key != null && !key.equals("")) {
            sb.append(" and ");
            sb.append(" (plateNo like ? or siterName like ? or factoryName like ? or productName like ? or companyName like ?) ");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
        }
        return sb.toString();
    }

    public static String buildWhere4OutPaper(Integer ID, String companyCode, String factoryCode, String dateFrom, String dateTo, String key, List<Object> argList) {
        StringBuilder sb = new StringBuilder();
        //List<Object> argList = new ArrayList<Object>(); //组织参数值
        if (ID != null) {
            sb.append(" and ID>?");
            argList.add(ID);
        }
        if (companyCode != null && !"".equals(companyCode) && !"请选择供应商".equals(companyCode)) {
            sb.append(" and companyCode=?");
            argList.add(companyCode);
        }
        if (factoryCode != null && !"".equals(factoryCode) && !"请选择客户".equals(factoryCode)) {
            sb.append(" and factoryCode=?");
            argList.add(factoryCode);
        }
        //时间查询以磅单时间为依据
        if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo == null || dateTo.trim().equals(""))) {

            //dateFrom = dateFrom.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateFrom);

        } else if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo != null && !dateTo.trim().equals(""))) {
            //开始日期和结束日期都有
            // dateFrom = dateFrom.replaceAll("-", "");
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime>=? and paperTime<=?");
            argList.add(dateFrom);
            argList.add(dateTo);
        } else if ((dateTo != null && !dateTo.trim().equals("")) && (dateFrom == null || dateFrom.trim().equals(""))) {
            //只有结束日期，则查询结束日期当天的数据
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateTo);
        }
        //模糊查询
        if (key != null && !key.equals("")) {
            sb.append(" and ");
            sb.append(" (businessCode like ? or sellContractCode like ? or factoryName like ? or companyName like ?) ");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
        }
        return sb.toString();
    }

    public static String buildWhere4PaySearch(Integer ID, String companyCode, String factoryCode, String dateFrom, String dateTo, String key, List<Object> argList) {
        StringBuilder sb = new StringBuilder();
        //List<Object> argList = new ArrayList<Object>(); //组织参数值
        if (ID != null) {
            sb.append(" and ID>?");
            argList.add(ID);
        }
        if (companyCode != null && !"".equals(companyCode) && !"请选择供应商".equals(companyCode)) {
            sb.append(" and companyCode=?");
            argList.add(companyCode);
        }
        if (factoryCode != null && !"".equals(factoryCode) && !"请选择客户".equals(factoryCode)) {
            sb.append(" and factoryCode=?");
            argList.add(factoryCode);
        }
        //时间查询以磅单时间为依据
        if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo == null || dateTo.trim().equals(""))) {

            //dateFrom = dateFrom.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateFrom);

        } else if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo != null && !dateTo.trim().equals(""))) {
            //开始日期和结束日期都有
            // dateFrom = dateFrom.replaceAll("-", "");
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime>=? and paperTime<=?");
            argList.add(dateFrom);
            argList.add(dateTo);
        } else if ((dateTo != null && !dateTo.trim().equals("")) && (dateFrom == null || dateFrom.trim().equals(""))) {
            //只有结束日期，则查询结束日期当天的数据
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateTo);
        }
        //模糊查询
        if (key != null && !key.equals("")) {
            sb.append(" and ");
            sb.append(" (code like ? or payWayName like ? or factoryName like ? or name like ? or payReason like ? or draftName like ?) ");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
        }
        return sb.toString();
    }

    public static String buildWhere4MoneyBack(Integer ID, String companyCode, String factoryCode, String dateFrom, String dateTo, String key, List<Object> argList) {
        StringBuilder sb = new StringBuilder();
        //List<Object> argList = new ArrayList<Object>(); //组织参数值
        if (ID != null) {
            sb.append(" and ID>?");
            argList.add(ID);
        }
        if (companyCode != null && !"".equals(companyCode) && !"请选择供应商".equals(companyCode)) {
            sb.append(" and companyCode=?");
            argList.add(companyCode);
        }
        if (factoryCode != null && !"".equals(factoryCode) && !"请选择客户".equals(factoryCode)) {
            sb.append(" and factoryCode=?");
            argList.add(factoryCode);
        }
        //时间查询以磅单时间为依据
        if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo == null || dateTo.trim().equals(""))) {

            //dateFrom = dateFrom.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateFrom);

        } else if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo != null && !dateTo.trim().equals(""))) {
            //开始日期和结束日期都有
            // dateFrom = dateFrom.replaceAll("-", "");
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime>=? and paperTime<=?");
            argList.add(dateFrom);
            argList.add(dateTo);
        } else if ((dateTo != null && !dateTo.trim().equals("")) && (dateFrom == null || dateFrom.trim().equals(""))) {
            //只有结束日期，则查询结束日期当天的数据
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateTo);
        }
        //模糊查询
        if (key != null && !key.equals("")) {
            sb.append(" and ");
            sb.append(" (factoryName like ? or companyName like ? or code like ? or bankNum like ?) ");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
        }
        return sb.toString();
    }

    public static String buildWhere4BuyInvoice(Integer ID, String companyCode, String factoryCode, String dateFrom, String dateTo, String key, List<Object> argList) {
        StringBuilder sb = new StringBuilder();
        //List<Object> argList = new ArrayList<Object>(); //组织参数值
        if (ID != null) {
            sb.append(" and ID>?");
            argList.add(ID);
        }
        if (companyCode != null && !"".equals(companyCode) && !"请选择供应商".equals(companyCode)) {
            sb.append(" and companyCode=?");
            argList.add(companyCode);
        }
        if (factoryCode != null && !"".equals(factoryCode) && !"请选择客户".equals(factoryCode)) {
            sb.append(" and factoryCode=?");
            argList.add(factoryCode);
        }
        //时间查询以磅单时间为依据
        if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo == null || dateTo.trim().equals(""))) {

            //dateFrom = dateFrom.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateFrom);

        } else if ((dateFrom != null && !dateFrom.trim().equals("")) && (dateTo != null && !dateTo.trim().equals(""))) {
            //开始日期和结束日期都有
            // dateFrom = dateFrom.replaceAll("-", "");
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime>=? and paperTime<=?");
            argList.add(dateFrom);
            argList.add(dateTo);
        } else if ((dateTo != null && !dateTo.trim().equals("")) && (dateFrom == null || dateFrom.trim().equals(""))) {
            //只有结束日期，则查询结束日期当天的数据
            //dateTo = dateTo.replaceAll("-", "");
            sb.append(" and ");
            sb.append(" paperTime=?");
            argList.add(dateTo);
        }
        //模糊查询
        if (key != null && !key.equals("")) {
            sb.append(" and ");
            sb.append(" (factoryName like ? or companyName like ? or businessCode like ?) ");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
            argList.add("%" + key + "%");
        }
        return sb.toString();
    }

    public static String buildWhere4Factory(String key) {
        StringBuilder sb = new StringBuilder();
        String arg = "%" + key + "%";
        if (key != null && !key.equals("")) {
            sb.append(" and ");
            sb.append(" (name like '").append(arg).append("' or address like '").append(arg).append("' or typeName like '").append(arg).append("' or bank like '").append(arg).append("')");
        }
        return sb.toString();
    }

    public static String buildWhere4BuyInvoiceVerify(String key) {
        StringBuilder sb = new StringBuilder();
        String arg = "%" + key + "%";
        if (key != null && !key.equals("")) {
            sb.append(" and ");
            sb.append(" (businessCode like '").append(arg).append("' or name like '").append(arg).append("')");
        }
        return sb.toString();
    }


}
